﻿using Dapper;
using Microsoft.Data.SqlClient;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using YfShlErp.Model;
using YfShlErp.Model1;
using SqlConnection = System.Data.SqlClient.SqlConnection;

namespace YfShlErp.DAL
{
    public class sql
    {
        private string connectionString = "Data Source=192.168.10.105;Initial Catalog=YFSHLerpDB;Integrated Security=True;TrustServerCertificate=True";

        /// <summary>
        /// 检查登录账号密码
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool CheckUserCount(string username, string password)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                var user = connection.Query<User>(
                    "SELECT * FROM Users WHERE UserName = @Username AND Password = @Password",
                    new { Username = username, Password = password }).FirstOrDefault();

                return user != null;
            }
        }

        /// <summary>
        /// 录入卖家信息
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="Phone"></param>
        /// <param name="Type"></param>
        /// <param name="weight"></param>
        /// <returns></returns>
        public bool EnteringSellersData(string Name, string Phone, string Type, double weight, double price)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                var query = "INSERT INTO SellerFruit (SellerName, SellerPhone, FruitType, Weight,Price) VALUES (@Name, @Phone, @Type, @Weight,@Price)";
                var result = connection.Execute(query, new { Name, Phone, Type, Weight = weight, Price = price });
                return result > 0;
            }
        }

        public IEnumerable<SellerFruit> GetSellersData(string NumberOrName, DateTime dateTime)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                var query = @"
                SELECT SellerName, SellerPhone, FruitType, Weight, EntryTime,Price
                FROM SellerFruit
                WHERE (SellerName = @NumberOrName OR SellerPhone = @NumberOrName)
                  AND CAST(EntryTime AS DATE) = CAST(@DateTime AS DATE)";
                var result = connection.Query<SellerFruit>(query, new { NumberOrName = NumberOrName, DateTime = dateTime }).ToList();
                return result;
            }
        }

        public IEnumerable<SellerFruit> GetSellersData(string NumberOrName)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                var query = @"
                SELECT SellerName, SellerPhone, FruitType, Weight, EntryTime,Price
                FROM SellerFruit
                WHERE (SellerName = @NumberOrName OR SellerPhone = @NumberOrName)";
                var result = connection.Query<SellerFruit>(query, new { NumberOrName = NumberOrName }).ToList();
                return result;
            }
        }

        public List<FruitPrice> GetFruitPrice()
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                var query = "SELECT * FROM FruitPrice";
                var result = connection.Query<FruitPrice>(query).ToList();
                return result;
            }
        }
    }
}